package com.caifu.mapper;

import com.caifu.pojo.ZcOrderingRecordDetails;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author Lyf
 * @since 2020-11-25
 */
public interface ZcOrderingRecordDetailsMapper extends BaseMapper<ZcOrderingRecordDetails> {

    /**
     * 功能描述: 根据用户编号和月份获取当月早餐和午餐的补助金额
     *
     * @auther: Lyf
     * @date: 2020/11/25 13:30
     * @param:
     * @return:
     */
    @Select("select (select ifnull(sum(b.subsidy),0) as subsidy from zc_ordering_record_details a left join zc_ordering_records b on a.record_id = b.id left join zc_ordering c on b.order_id = c.id where a.user_no = #{userNo} and a.month = #{month} and a.type = '1' and b.status = '1' and c.status = '3') as wucan,(select ifnull(sum(b.subsidy),0) as subsidy from zc_ordering_record_details a left join zc_ordering_records b on a.record_id = b.id left join zc_ordering c on b.order_id = c.id where a.user_no = #{userNo} and a.month = #{month} and a.type = '2' and b.status = '1' and c.status = '3') as wancan from dual")
    Map<String,String> getSubsidyStatistics(@Param("userNo") String userNo,@Param("month") String month);

    @Select(" <script>"+
            " select a.id,DATE_FORMAT( a.confirm_time, '%Y-%m-%d' ) AS confirm_time,a.user_no,f.org_name,b.user_type,d.user_name as zd_user_name,e.user_name AS fd_user_name,d.user_sex AS zd_user_sex,e.sex AS fd_user_sex,d.birth_date AS zd_birth_date,DATE_FORMAT( e.brith_date, '%Y-%m-%d' ) AS fd_birth_date,a.month,a.day,b.subsidy,b.meals,b.delivery_fee,b.cost,b.type,b.address,a.type as food_type,a.confirm_status as distribution_status from zc_ordering_record_details a left join zc_ordering_records b on a.record_id = b.id left join zc_ordering c on b.order_id = c.id left join user_info d ON a.user_no = d.user_no left join zc_no_archives_user e ON a.user_no = e.user_no left join sys_organization f on c.org_id = f.org_id where c.status ='3' and b.status = '1' and c.org_id in ("+
            " <foreach collection='orgList' index='index' item='item' open='' separator=' , ' close=''>#{item}</foreach>" +
            " )" +
            " <if test='name != null and name != &quot;&quot;'>"+
            " and (d.user_name like concat('%',#{name},'%') or e.user_name like concat('%',#{name},'%'))"+
            " </if >" +
            " <if test='startDate != null and startDate != &quot;&quot;'>"+
            " and concat(a.month,a.day) &gt;= #{startDate}"+
            " </if >" +
            " <if test='endDate != null and endDate != &quot;&quot;'>"+
            " and concat(a.month,a.day) &lt;= #{endDate}"+
            " </if >" +
            " <if test='userType != null and userType != &quot;&quot;'>"+
            " and b.user_type = #{userType}"+
            " </if >" +
            " <if test='type != null and type != &quot;&quot;'>"+
            " and b.type = #{type}"+
            " </if >" +
            " <if test='foodType != null and foodType != &quot;&quot;'>"+
            " and b.food_type = #{foodType}"+
            " </if >" +
            " <if test='orderStatus != null and orderStatus != &quot;&quot;'>"+
            " and a.confirm_status = #{orderStatus}"+
            " </if >" +
            " order by a.month desc,c.org_id,a.day,b.food_type,b.user_type,d.user_name,d.user_no,e.user_name,e.user_no limit #{start},#{pageSize}" +
            " </script>")
    List<Map<String,String>> getList(@Param("start") int start,@Param("pageSize") int pageSize,@Param("name") String name,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("orgList") List<String> orgList,@Param("userType") String userType,@Param("type") String type,@Param("foodType") String foodType,@Param("orderStatus") String orderStatus);

    @Select(" <script>"+
            " select count(*) as count from zc_ordering_record_details a left join zc_ordering_records b on a.record_id = b.id left join zc_ordering c on b.order_id = c.id left join user_info d ON a.user_no = d.user_no left join zc_no_archives_user e ON a.user_no = e.user_no where c.status ='3' and b.status = '1' and c.org_id in ("+
            " <foreach collection='orgList' index='index' item='item' open='' separator=' , ' close=''>#{item}</foreach>" +
            " )" +
            " <if test='name != null and name != &quot;&quot;'>"+
            " and (d.user_name like concat('%',#{name},'%') or e.user_name like concat('%',#{name},'%'))"+
            " </if >" +
            " <if test='startDate != null and startDate != &quot;&quot;'>"+
            " and concat(a.month,a.day) &gt;= #{startDate}"+
            " </if >" +
            " <if test='endDate != null and endDate != &quot;&quot;'>"+
            " and concat(a.month,a.day) &lt;= #{endDate}"+
            " </if >" +
            " <if test='userType != null and userType != &quot;&quot;'>"+
            " and b.user_type = #{userType}"+
            " </if >" +
            " <if test='type != null and type != &quot;&quot;'>"+
            " and b.type = #{type}"+
            " </if >" +
            " <if test='foodType != null and foodType != &quot;&quot;'>"+
            " and b.food_type = #{foodType}"+
            " </if >" +
            " <if test='orderStatus != null and orderStatus != &quot;&quot;'>"+
            " and a.confirm_status = #{orderStatus}"+
            " </if >" +
            " </script>")
    int getListCount(@Param("name") String name,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("orgList") List<String> orgList,@Param("userType") String userType,@Param("type") String type,@Param("foodType") String foodType,@Param("orderStatus") String orderStatus);

    @Select("select ifnull(sum(b.cost),0) as cost,ifnull(sum(b.subsidy),0) as subsidy from zc_ordering_record_details a left join zc_ordering_records b on a.record_id = b.id left join zc_ordering c on b.order_id = c.id where c.status = '3' and b.status = '1' and b.user_type = '1'")
    Map<String,String> costStatistics();

    @Select(" <script>"+
            " select count(distinct a.user_no) as count from zc_ordering_records a left join zc_ordering b on a.order_id = b.id where b.status ='3' and a.status = '1' and a.user_type = '1' and a.month = #{month} and b.org_id in (" +
            " <foreach collection='orgList' index='index' item='item' open='' separator=' , ' close=''>#{item}</foreach>" +
            " )" +
            " </script>")
    String properStatistics(@Param("orgList") List<String> orgList,@Param("month") String month);

    @Select("select t.ageproportion as age_str,count(*) as count from ( SELECT CASE when c.age >=60 and c.age <= 69 then '60-69' when c.age >=70 and c.age <= 79 then '70-79' when c.age >=80 and c.age <= 89 then '80-89' when c.age >=90 and c.age <= 99 then '90-99' when age >= 100 then '100+' else 'other' end as ageproportion from (select distinct user_no from zc_ordering_records a left join zc_ordering b on a.order_id = b.id where b.status ='3' and a.status = '1'  and a.user_type = '1') as ab left join user_info c on ab.user_no = c.user_no )t GROUP BY t.ageproportion")
    List<Map<String,String>> ageStatistics();

    @Select(" <script>"+
            " select ifnull(sum(b.subsidy),0) as subsidy,a.month from zc_ordering_record_details a left join zc_ordering_records b on a.record_id = b.id left join zc_ordering c on b.order_id = c.id where c.status = '3' and b.status = '1' and b.user_type = '1' and c.org_id in (" +
            " <foreach collection='orgList' index='index' item='item' open='' separator=' , ' close=''>#{item}</foreach>" +
            ") group by a.month order by a.month" +
            " </script>")
    List<Map<String,String>> subsidyStatistics(@Param("orgList") List<String> orgList);

    @Select(" <script>"+
            " select ifnull(count(distinct b.user_no),0) as count,a.month from zc_ordering_record_details a left join zc_ordering_records b on a.record_id = b.id left join zc_ordering c on b.order_id = c.id where c.status = '3' and b.status = '1' and b.user_type = '1' and c.org_id in (" +
            " <foreach collection='orgList' index='index' item='item' open='' separator=' , ' close=''>#{item}</foreach>" +
            ") group by a.month order by a.month" +
            " </script>")
    List<Map<String,String>> mealsCountStatistics(@Param("orgList") List<String> orgList);

    /**
     * 父查子
     * 2020年8月9日
     * codeBy szt
     */
    @Select("SELECT u2.org_id FROM( SELECT @ids AS p_ids, (SELECT @ids := GROUP_CONCAT(org_id) FROM sys_organization WHERE FIND_IN_SET(parent_org_id, @ids)) AS c_ids, @l := @l+1 AS LEVEL FROM sys_organization, (SELECT @ids := #{inParentOrgId}, @l := 0 ) b  WHERE @ids IS NOT NULL ) u1 JOIN sys_organization u2 ON FIND_IN_SET(u2.org_id, u1.p_ids) AND u2.state='1' ")
    List<String> getListByParentId(@Param("inParentOrgId") String inParentOrgId);

}
